package com.pls.excelwriter;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Locale;

import com.pls.entity.EmployeeSeat;
import com.pls.main.Configuration;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class WriteExcel {

	private WritableCellFormat tahomaBoldUnderline;
	private WritableCellFormat tahoma;
	private String inputFile;

	public void write(List<EmployeeSeat> employeeSeats, EmployeeSeat employee)
			throws IOException, WriteException {
		File file = new File(Configuration.fileDirectory + employee.getEmployee().getId() + ".xls");
		WorkbookSettings wbSettings = new WorkbookSettings();

		wbSettings.setLocale(new Locale("en", "EN"));
		WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
		workbook.createSheet("Seat Plan", 0);
		WritableSheet excelSheet = workbook.getSheet(0);
		createLabel(excelSheet);
		createContent(excelSheet, employeeSeats, employee);

		workbook.write();
		workbook.close();
		
		System.out.println("successfully exported as excel file!");
		System.out.println();
	}

	private void createLabel(WritableSheet sheet) throws WriteException {
		// Lets create a tahoma font
		WritableFont tahoma12pt = new WritableFont(WritableFont.TAHOMA, 10);
		// Define the cell format
		tahoma = new WritableCellFormat(tahoma12pt);
		// Lets automatically wrap the cells
		tahoma.setWrap(true);

		// Create create a bold font with unterlines
		WritableFont tahoma12ptBoldUnderline = new WritableFont(
				WritableFont.TAHOMA, 10, WritableFont.BOLD, false,
				UnderlineStyle.SINGLE);
		tahomaBoldUnderline = new WritableCellFormat(tahoma12ptBoldUnderline);
		// Lets automatically wrap the cells
		tahomaBoldUnderline.setWrap(true);

		CellView cv = new CellView();
		cv.setFormat(tahoma);
		cv.setFormat(tahomaBoldUnderline);
		cv.setAutosize(true);
		//
		// // Write a few headers
		// addCaption(sheet, 0, 0, "Header 1");
		// addCaption(sheet, 1, 0, "This is another header");

	}

	private void createContent(WritableSheet sheet,
			List<EmployeeSeat> employeeSeats, EmployeeSeat empSeat)
			throws WriteException, RowsExceededException {
		
		int index = 0;

		for (int row = 1; row < 6; row++) {
			try {
				while (employeeSeats.get(index).getRow() == row) {

					String seatCode = employeeSeats.get(index).getSeatCode();
					String employeeCode = "";

					if (employeeSeats.get(index).getEmployee().getId() == 0) {
						employeeCode = "Not Assigned";

					} else if (employeeSeats.get(index).getEmployee().getId() == empSeat
							.getEmployee().getId()) {
						employeeCode = "*EMP"
								+ employeeSeats.get(index).getEmployee()
										.getId() + "*";
					} else {
						employeeCode = "EMP"
								+ employeeSeats.get(index).getEmployee()
										.getId();
					}

					String localNo = (employeeSeats.get(index).getLocalNo() == null) ? ""
							: employeeSeats.get(index).getLocalNo();

					addLabel(sheet, employeeSeats.get(index).getCol(),
							employeeSeats.get(index).getRow(), seatCode
									+ "\r\n" + employeeCode + "\r\n" + localNo);

					index++;

					if (employeeSeats.size() == index) {
						break;
					}
				}

			} catch (IndexOutOfBoundsException e) {
				break;
			}
		}

		// // Write a few number
		// for (int i = 1; i < 10; i++) {
		// // First column
		// addNumber(sheet, 0, i, i + 10);
		// // Second column
		// addNumber(sheet, 1, i, i * i);
		// }
		// // Lets calculate the sum of it
		// StringBuffer buf = new StringBuffer();
		// buf.append("SUM(A2:A10)");
		// Formula f = new Formula(0, 10, buf.toString());
		// sheet.addCell(f);
		// buf = new StringBuffer();
		// buf.append("SUM(B2:B10)");
		// f = new Formula(1, 10, buf.toString());
		// sheet.addCell(f);
		//
		// // Now a bit of text
		// for (int i = 12; i < 20; i++) {
		// // First column
		// addLabel(sheet, 0, i, "Boring text " + i);
		// // Second column
		// addLabel(sheet, 1, i, "Another text");
		// }
	}

	// private void addCaption(WritableSheet sheet, int column, int row, String
	// s)
	// throws RowsExceededException, WriteException {
	// Label label;
	// label = new Label(column, row, s, tahomaBoldUnderline);
	// sheet.addCell(label);
	// }
	//
	// private void addNumber(WritableSheet sheet, int column, int row,
	// Integer integer) throws WriteException, RowsExceededException {
	// Number number;
	// number = new Number(column, row, integer, tahoma);
	// sheet.addCell(number);
	// }

	private void addLabel(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		label = new Label(column, row, s, tahoma);
		sheet.addCell(label);
	}

	// public static void main(String[] args) throws WriteException, IOException
	// {
	// WriteExcel test = new WriteExcel();
	// // test.write();
	// System.out
	// .println("Please check the result file under c:/temp/lars.xls ");
	// }

}
